package nxu.it.movieapi.service.pg;

import nxu.it.api.common.Util;
import nxu.it.api.common.result.BasePage;
import nxu.it.api.common.result.Pageable;
import nxu.it.api.model.Cast;
import nxu.it.api.model.CastRole;
import nxu.it.api.model.Filmography;
import nxu.it.api.model.Genre;
import nxu.it.api.model.Movie;
import nxu.it.api.model.SimpleMovie;
import nxu.it.api.service.GenreService;
import nxu.it.api.service.MovieCastService;
import nxu.it.api.service.MovieService;
import nxu.it.api.service.param.MoviePageQueryParam;
import nxu.it.movieapi.service.pg.dao.MoviePgDao;
import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.statement.UnableToExecuteStatementException;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Component;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

@Component
public class MovieServicePgImpl implements MovieService {
    private static final Logger logger = LoggerFactory.getLogger(MovieServicePgImpl.class);
    private final Jdbi jdbi;
    private final GenreService genreService;
    private final MovieCastService movieCastService;

    @Autowired
    public MovieServicePgImpl(@Qualifier("createSecondJdbi") Jdbi jdbi, GenreService genreService, MovieCastService movieCastService) {
        this.jdbi = jdbi;
        this.genreService = genreService;
        this.movieCastService = movieCastService;
        // 确保安装了SqlObjectPlugin插件
        this.jdbi.installPlugin(new SqlObjectPlugin());
    }

    @Override
    public Optional<Movie> findById(Integer movieId) {
        try {
            Optional<Movie> movieOpt = jdbi.withExtension(MoviePgDao.class, dao -> dao.findById(movieId));

            if (movieOpt.isPresent()) {
                Movie movie = movieOpt.get();
                List<Genre> genreList = genreService.findByMovieId(movieId);
                List<Cast> directors = movieCastService.findCastByMovieIdAndRole(movieId, CastRole.DIRECTOR);
                List<Cast> actors = movieCastService.findCastByMovieIdAndRole(movieId, CastRole.ACTOR);

                movie.setGenres(genreList);
                movie.setDirectors(directors);
                movie.setActors(actors);

                return Optional.of(movie);
            }
        } catch (Exception e) {
            logger.error("Error finding movie by ID: {}", movieId, e);
        }

        return Optional.empty();
    }

    @Override
    public Pageable<SimpleMovie> page(MoviePageQueryParam pageQueryParam) {
        int page = pageQueryParam.getPageNumber();
        int size = pageQueryParam.getPageSize();
        int offset = (page - 1) * size;
        Map<String, Object> params = new HashMap<>();

        // 构建WHERE子句
        StringBuilder whereClause = new StringBuilder("WHERE 1 = 1");
        String originalTitle = pageQueryParam.getOriginalTitle();
        if (originalTitle != null && !originalTitle.isEmpty()) {
            whereClause.append(" AND original_title ILIKE '%' || :originalTitle || '%'");
            params.put("originalTitle", originalTitle);
        }
        String chineseTitle = pageQueryParam.getChineseTitle();
        if (chineseTitle != null && !chineseTitle.isEmpty()) {
            whereClause.append(" AND chinese_title ILIKE '%' || :chineseTitle || '%'");
            params.put("chineseTitle", chineseTitle);
        }
        String region = pageQueryParam.getRegion();
        if (region != null && !region.isEmpty()) {
            whereClause.append(" AND region ILIKE '%' || :region || '%'");
            params.put("region", region);
        }
        String language = pageQueryParam.getLanguage();
        if (language != null && !language.isEmpty()) {
            whereClause.append(" AND language ILIKE '%' || :language || '%'");
            params.put("language", language);
        }

        // 使用反射获取MoviePageQueryParam类的orderFields字段
        String[] allowedOrderFields = null;
        try {
            Field orderFieldsField = MoviePageQueryParam.class.getDeclaredField("orderFields");
            orderFieldsField.setAccessible(true); // 设置私有字段可访问
            allowedOrderFields = (String[]) orderFieldsField.get(pageQueryParam);
        } catch (NoSuchFieldException | IllegalAccessException e) {
            logger.error("Error accessing orderFields via reflection", e);
            // 处理错误，可能返回空列表或默认排序
        }

        // 构建ORDER BY子句
        StringBuilder orderByClause = new StringBuilder();
        String sortField = pageQueryParam.getOrderField();
        String sortOrder = pageQueryParam.getOrderDirection();

        if (allowedOrderFields != null && sortField != null && !sortField.isEmpty() && Util.equalsAnyIgnoreCase(sortField, allowedOrderFields)) {
            orderByClause.append(sortField);
            if (sortOrder != null && (sortOrder.equalsIgnoreCase("ASC") || sortOrder.equalsIgnoreCase("DESC"))) {
                orderByClause.append(" ").append(sortOrder);
            } else {
                orderByClause.append(" ASC"); // 默认排序方向
            }
        } else {
            orderByClause.append("id ASC"); // 如果sortField不是预定义的字段，则默认按id排序
        }

        List<SimpleMovie> movies;
        int total;
        try {
            total = jdbi.withExtension(MoviePgDao.class, dao ->
                    dao.complexCount(whereClause.toString(), params)
            );
            movies = jdbi.withExtension(MoviePgDao.class, dao ->
                    dao.complexPage(whereClause.toString(), orderByClause.toString(), size, offset, params)
            );

            // 计算总页数，确保至少为1
            int totalPage = (total + size - 1) / size; // 使用向上取整来计算页数

            // 添加日志记录查询结果
            logger.info("Total movies found: {}", total);
            logger.info("Total pages calculated: {}", totalPage);

            // 构造 BasePage 对象
            return new BasePage<>(movies, pageQueryParam.getPageNumber(), size, total, totalPage);
        } catch (UnableToExecuteStatementException e) {
            logger.error("Error executing statement for pagination", e);
            return new BasePage<>(List.of(), pageQueryParam.getPageNumber(), size, 0, 0);

        }
    }
}
